package com.bifrii.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import com.bifrii.transferObjects.Event;
import com.bifrii.transferObjects.Route;
import com.bifrii.transferObjects.User;

public class JDBCRouteDAO implements IRouteDAO {

	@Override
	public void insert(Route r) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "INSERT INTO Route (oid, idEvent, place, date) VALUES (?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, r.getOid());
            stmt.setInt(2, r.getIdEvent());
            stmt.setString(3, r.getPlace());
            stmt.setString(4, r.getDate());
            stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }

	}

	@Override
	public void delete(Integer idRoute) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		
		String sql = "DELETE FROM event WHERE (idEvent = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idRoute);
            stmt.executeUpdate();
            
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
            
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}

	@Override
	public Route select(Integer idEvent) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "SELECT * FROM Route WHERE (idEvent = ? ) ";
        PreparedStatement stmt = null;
        ResultSet result = null;
        Route r = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idEvent);

            result = stmt.executeQuery();

            result.next();
            r = new Route();
            r.setIdEvent(result.getInt("idEvent"));
            r.setPlace(result.getString("place"));
            r.setDate(result.getString("date"));
          
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
        return r;
	}

	@Override
	public List<Route> selectAll() {
		Connection conn = ConnectionManager.getInstance().checkOut();
        PreparedStatement stmt = null;
        List<Route> searchResults = new LinkedList<Route>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Route order by idEvent desc";
            stmt = conn.prepareStatement(sql);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Route r = new Route();
                r.setOid(result.getString("oid"));
                r.setIdEvent(result.getInt("idEvent"));
                r.setPlace(result.getString("place"));
                r.setDate(result.getString("date"));
                searchResults.add(r);
            }
            
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }

        }
        return searchResults;
	}
	
	public List<Route> selectAll(Integer idEvent) {
		Connection conn = ConnectionManager.getInstance().checkOut();
        PreparedStatement stmt = null;
        List<Route> searchResults = new LinkedList<Route>();
        ResultSet result = null;

        try {

            String sql = "SELECT * FROM Route where idEvent = ? order by idEvent desc";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idEvent);
            stmt.executeQuery();
            result = stmt.executeQuery();

            while (result.next()) {
                Route r = new Route();
                r.setOid(result.getString("oid"));
                r.setIdEvent(result.getInt("idEvent"));
                r.setPlace(result.getString("place"));
                r.setDate(result.getString("date"));
                searchResults.add(r);
            }
            
        } catch (SQLException ex) {
            System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            ConnectionManager.getInstance().checkIn(conn);
            try {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }

        }
        return searchResults;
	}
}
